SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_PWSyncDashBoard_PW10DBSyncStatsCheck]
	@DBName VARCHAR(100)
AS
BEGIN
	
	DECLARE @sql NVARCHAR(MAX)
	
	DECLARE @CAPNAME VARCHAR(100)
	
	DECLARE @PWAP_AppSchemas VARCHAR(255)
	DECLARE @PWAP_location VARCHAR(255)

	SET @CAPNAME = ''
	
	CREATE TABLE #LatestSyncStatsID (SyncStatsID INT)
			
	SET @SQL = 'SELECT @PWAP_AppSchemasR = ServerSettingsValue FROM ' + @DBName + '..cw_Settings_Server WHERE ServerSettings = ''PWAP_AppSchemas'''
	SET @PWAP_AppSchemas = ''
	
	EXECUTE sp_executesql @SQL, N'@PWAP_AppSchemasR VARCHAR(255) OUTPUT', @PWAP_AppSchemasR = @PWAP_AppSchemas OUTPUT
	
	SET @SQL = 'SELECT @PWAP_locationR = ServerSettingsValue FROM ' + @DBName + '..cw_Settings_Server WHERE ServerSettings = ''PWAP_location'''
	SET @PWAP_location = ''
	
	EXECUTE sp_executesql @SQL, N'@PWAP_locationR VARCHAR(255) OUTPUT', @PWAP_locationR = @PWAP_location OUTPUT
	
	SET @CAPName = dbo.[GetCAPName](@PWAP_location)
	
	SET @SQL = 'INSERT INTO #LatestSyncStatsID (SyncStatsID)
				SELECT SyncStatsID
				FROM ' + @DBName + '..cw_SyncStats WHERE SyncStatsID NOT IN 
												(
													SELECT SyncStatsID FROM DnnProduct_PWSyncDashBoard_LastSyncStatuses 
													WHERE DBName = ''' + @DBName + '''
												)
				ORDER BY SyncStatsID DESC'

	EXECUTE sp_executesql @SQL

	SET @SQL = 'INSERT INTO DnnProduct_PWSyncDashBoard_LastSyncStatuses
					([DBName], [PWVersion], [SyncStatsID], [CAPName], [PWAP_AppSchemas], [PWAP_location], [RunTime], [Succeeded], [FailedStep]
					,[FailAt], [ErrorDetail], [StepDetails], [DelRec], [ModRec], [NewRec], [Timestamp]
					)
				SELECT ''' + @DBName + ''', ''PW10'', SyncStatsID, ''' + @CAPNAME + ''', ''' + @PWAP_AppSchemas + ''', '''+ @PWAP_location + ''', 
						RunTime, 
						(CASE WHEN step1 = 0 OR step2 = 0 OR step3 = 0 OR step4 = 0 OR step5 = 0 OR step6 = 0 OR step7 = 0 OR step8 = 0 OR step9 = 0 OR step10 = 0 OR step11 = 0 THEN 0 ELSE 1 END) AS Succeeded,
						FailedStep, FailedAt, ErrorDetail, 
						(CONVERT(VARCHAR(1),step1) + ''|'' + CONVERT(VARCHAR(1),step2) + ''|'' + CONVERT(VARCHAR(1),step3) + ''|'' + 
							CONVERT(VARCHAR(1),step4) + ''|'' + CONVERT(VARCHAR(1),step5) + ''|'' + CONVERT(VARCHAR(1),step6) + ''|'' + 
							CONVERT(VARCHAR(1),step7) + ''|'' + CONVERT(VARCHAR(1),step8) + ''|'' + CONVERT(VARCHAR(1),step9) + ''|'' +
							CONVERT(VARCHAR(1),step10) + ''|'' + CONVERT(VARCHAR(1),step11) + ''|'' + CONVERT(VARCHAR(1),step12) + ''|'' +
							CONVERT(VARCHAR(1),step13) + ''|'' + CONVERT(VARCHAR(1),step14) + ''|'' + CONVERT(VARCHAR(1),step15) + ''|'' +
							CONVERT(VARCHAR(1),step16) + ''|'' + CONVERT(VARCHAR(1),step17)) AS StepDetails, 
						(SELECT TOP 1 DelRec FROM ' + @DBName + '..cw_WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS DelRec, 
						(SELECT TOP 1 ModRec FROM ' + @DBName + '..cw_WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS ModRec,
						(SELECT TOP 1 NewRec FROM ' + @DBName + '..cw_WebSync WHERE SyncStatsID = sstats.SyncStatsID) AS NewRec,
						GETDATE()
				FROM ' + @DBName + '..cw_SyncStats sstats
				WHERE SyncStatsID IN (SELECT SyncStatsID FROM #LatestSyncStatsID)
				ORDER BY SyncStatsID DESC'
	
	EXECUTE sp_executesql @SQL

	DROP TABLE #LatestSyncStatsID

END
GO
